// .........................................................................
// Title: ciq_characteristics.do
//
// Imports and cleans bond characteristics data from S&P Global Capital IQ
// .........................................................................

cap mkdir $tmp/ciq_import

* -------------------------------------------
* Import bond characteristics from Capital IQ
* -------------------------------------------


* import ciq data
import delimited using "$raw/ciq/CIQ-Characteristics-CUSIP.csv", clear varnames(1) case(lower)
save "$tmp/ciq_import/ciq_characteristics_cusip", replace

import delimited using "$raw/ciq/CIQ-Characteristics-ISIN.csv", clear varnames(1) case(lower)
save "$tmp/ciq_import/ciq_characteristics_isin", replace

import delimited using "$raw/ciq/CIQ-Duration.csv", clear varnames(1) case(lower)    
save "$tmp/ciq_import/ciq_duration", replace

import delimited using "$raw/ciq/CIQ-Amounts.csv", clear varnames(1) case(lower)    
save "$tmp/ciq_import/ciq_amounts", replace

import delimited using "$raw/ciq/CIQ-Ratings.csv", clear varnames(1) case(lower)    
save "$tmp/ciq_import/ciq_ratings", replace


* static characteristics: merge files and consolidate cusip/isin versions
use "$tmp/ciq_import/ciq_characteristics_cusip", clear
mmerge cusip using "$tmp/ciq_import/ciq_characteristics_isin", unmatched(m) uname(i_)
drop _merge
foreach var of varlist iq_* {
    cap replace `var' = "" if `var' == "(Invalid Identifier)"
    replace `var' = i_`var' if missing(`var')
}
drop i_*
cap drop _*


* clean data and save
foreach var of varlist iq_first_int_date iq_maturity_date iq_offer_date iq_dated_date {
    replace `var' = "" if `var' == "12/31/1899"
}
gen _iq_maturity_date = date(iq_maturity_date, "MDY")
gen _iq_offer_date = date(iq_offer_date, "MDY")
gen _iq_dated_date = date(iq_dated_date, "MDY")
gen _iq_first_int_date = date(iq_first_int_date, "MDY")
format %td _iq_dated_date _iq_maturity_date _iq_offer_date _iq_first_int_date
drop iq_dated_date iq_offer_date iq_maturity_date iq_first_int_date
rename _* *
drop iq_amt_out
drop iq_benchmark_security iq_benchmark_sprd
drop if iq_security_name == "(Invalid Identifier)"
replace iq_security_type = "" if iq_security_type == "0"
replace iq_security_level = "" if iq_security_level == "0"
replace iq_issue_currency = "" if iq_issue_currency == "0"
replace iq_issuer = "" if iq_issuer == "0"
replace iq_offer_coupon_type = "" if iq_offer_coupon_type == "0"
replace iq_offer_amount = "" if iq_offer_amount == "0"
replace iq_principal_amt = "" if iq_principal_amt == "0"
replace iq_security_features = "" if iq_security_features == "0"
replace iq_ownership = "" if iq_ownership == "0"
replace iq_callable = "" if iq_callable == "0"
replace iq_call_feature = "" if iq_call_feature == "0"
replace iq_pmt_freq = "" if iq_pmt_freq == "0"
replace iq_pmt_freq = "" if iq_pmt_freq == "N/A"
save "$tmp/ciq_static_characteristics", replace


* program to process CIQ static characteristics
cap program drop process_ciq_characteristics
program process_ciq_characteristics

    gen bc_is_senior = .
    replace bc_is_senior = 1 if inlist(iq_security_level, "Senior Secured", "Senior Subordinate", "Senior Unsecured", "Preferred")
    replace bc_is_senior = 0 if inlist(iq_security_level, "Junior Subordinate", "Not Ranked", "Subordinate", "Junior", "Second Lien")
    assert ~missing(bc_is_senior) if ~missing(iq_security_level)

    gen bc_dilution_protection = 0
    replace bc_dilution_protection = 1 if strpos(iq_security_features, "Protection Against Dilution")

    gen bc_is_exchangeable = 0
    replace bc_is_exchangeable = 1 if strpos(iq_security_features, "Exchangeable")

    gen bc_is_puttable = 0
    replace bc_is_puttable = 1 if strpos(iq_security_features, "Puttable")
    replace bc_is_puttable = 1 if strpos(iq_security_features, "Putable")

    gen bc_is_144a = 0
    replace bc_is_144a = 1 if strpos(iq_security_features, "144A")
    replace bc_is_144a = 1 if strpos(iq_security_features, "Private Placement")

    gen bc_is_redeemable = 0
    replace bc_is_redeemable = 1 if strpos(iq_security_features, "Redeemable")
    replace iq_security_features = subinstr(iq_security_features, "Redeemable", "", .)

    gen bc_sinking_fund = 0
    replace bc_sinking_fund = 1 if strpos(iq_security_features, "Sinking Fund")
    replace iq_security_features = subinstr(iq_security_features, "Sinking Fund", "", .)

    gen bc_bankruptcy = 0
    replace bc_bankruptcy = 1 if strpos(iq_security_features, "Bankruptcy")
    replace iq_security_features = subinstr(iq_security_features, "Bankruptcy", "", .)

    gen bc_plan_confirmed = 0
    replace bc_plan_confirmed = 1 if strpos(iq_security_features, "Plan Confirmed")
    replace iq_security_features = subinstr(iq_security_features, "Plan Confirmed", "", .)

    gen bc_guaranteed = 0
    replace bc_guaranteed = 1 if strpos(iq_security_features, "Guaranteed")
    replace iq_security_features = subinstr(iq_security_features, "Guaranteed", "", .)

    gen bc_registration_rights = 0
    replace bc_registration_rights = 1 if strpos(iq_security_features, "Registration Rights")
    replace iq_security_features = subinstr(iq_security_features, "Registration Rights", "", .)

    gen bc_in_default = 0
    replace bc_in_default = 1 if strpos(iq_security_features, "In Default")
    replace iq_security_features = subinstr(iq_security_features, "In Default", "", .)

    gen bc_is_accredited = 0
    replace bc_is_accredited = 1 if strpos(iq_security_features, "Accredited")
    replace iq_security_features = subinstr(iq_security_features, "Accredited", "", .)

    gen bc_is_perpetual = 0
    replace bc_is_perpetual = 1 if strpos(iq_security_features, "Perpetual")
    replace iq_security_features = subinstr(iq_security_features, "Perpetual", "", .)

    gen bc_is_reinstated = 0
    replace bc_is_reinstated = 1 if strpos(iq_security_features, "Reinstated")
    replace iq_security_features = subinstr(iq_security_features, "Reinstated", "", .)

    gen bc_is_floating = 0
    replace bc_is_floating = 1 if iq_offer_coupon_type == "Variable"

    gen bc_is_callable = 0 if iq_callable == "No"
    replace bc_is_callable = 1 if iq_callable == "Yes"

    replace iq_security_features = subinstr(iq_security_features, "Convertible", "", .)
    replace iq_security_features = subinstr(iq_security_features, "Preferred Security", "", .)
    replace iq_security_features = subinstr(iq_security_features, "144A", "", .)
    replace iq_security_features = subinstr(iq_security_features, "Private Placement", "", .)
    replace iq_security_features = subinstr(iq_security_features, "Puttable", "", .)
    replace iq_security_features = subinstr(iq_security_features, "Non-Investment Grade", "", .)
    replace iq_security_features = subinstr(iq_security_features, "Investment Grade", "", .)
    replace iq_security_features = subinstr(iq_security_features, "Putable", "", .)
    replace iq_security_features = subinstr(iq_security_features, "Callable", "", .)
    replace iq_security_features = subinstr(iq_security_features, "Exchangeable", "", .)
    replace iq_security_features = subinstr(iq_security_features, "Protection Against Dilution", "", .)

    drop iq_security_features iq_offer_coupon_type

end


* process the characteristics file
use "$tmp/ciq_static_characteristics", clear
process_ciq_characteristics
save "$tmp/ciq_static_characteristics_processed", replace


* clean ciq duration
use "$tmp/ciq_import/ciq_duration", clear
reshape long duration_, i(cusip) j(_date) string
rename duration_ duration
gen date_m = mofd(date(_date, "MY"))
format %tm date_m
keep cusip date_m duration
order cusip date_m duration
drop if duration == "(Invalid Identifier)"
drop if duration == "#REFRESH"
destring duration, replace
drop if duration == 0
save "$tmp/ciq_clean_duration", replace


* clean ciq values outstanding
use "$tmp/ciq_import/ciq_amounts", clear
drop if value_2020 == "(Invalid Identifier)"
drop iq_security_name isin
reshape long value_, i(cusip) j(year)
rename value_ amount_outstanding
destring amount_outstanding, replace
save "$tmp/ciq_clean_amounts", replace


* clean ciq ratings
use "$tmp/ciq_import/ciq_ratings", clear
drop iq_issue_rating_static
reshape long iq_issue_rating_, i(cusip) j(year)
rename iq_issue_rating_ iq_issue_rating_pm
gen iq_issue_rating = iq_issue_rating_pm


* granular ratings version
replace iq_issue_rating_pm = "" if iq_issue_rating_pm == "0"
replace iq_issue_rating_pm = "" if iq_issue_rating_pm == "(Invalid Identifier)"
replace iq_issue_rating_pm = subinstr(iq_issue_rating_pm, " (sf)", "", .)
replace iq_issue_rating_pm = subinstr(iq_issue_rating_pm, "/A-2", "", .)
replace iq_issue_rating_pm = subinstr(iq_issue_rating_pm, "/NR", "", .)
replace iq_issue_rating_pm = subinstr(iq_issue_rating_pm, "/A-3", "", .)
replace iq_issue_rating_pm = subinstr(iq_issue_rating_pm, "/A-1+", "", .)
replace iq_issue_rating_pm = subinstr(iq_issue_rating_pm, "/A-1+", "", .)


* coarse ratings version
replace iq_issue_rating = "" if iq_issue_rating == "0"
replace iq_issue_rating = "" if iq_issue_rating == "(Invalid Identifier)"
replace iq_issue_rating = subinstr(iq_issue_rating, " (sf)", "", .)
replace iq_issue_rating = subinstr(iq_issue_rating, "/A-2", "", .)
replace iq_issue_rating = subinstr(iq_issue_rating, "/NR", "", .)
replace iq_issue_rating = subinstr(iq_issue_rating, "/A-3", "", .)
replace iq_issue_rating = subinstr(iq_issue_rating, "+", "", .)
replace iq_issue_rating = subinstr(iq_issue_rating, "-", "", .)
save "$tmp/ciq_clean_ratings", replace

